const mysql = require('../db/mysql57');

const hospitalDao = {
    async addHospitalAndAdmin(reqId, hospital, department, departmentID, cityId, name, userType, phone, account, password) {
        let connection, result, sql, data, newUser;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务失败`);
            await mysql.rollbackTransaction(connection);
            throw new Error("连接数据库失败 请联系系统管理员");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务成功`);
        // 尝试插入一个新的用户
        try {
            sql = `INSERT INTO User (userTypeId, name, phone, account, password) VALUES ((
                    select id from UserType where name = ?
                ), ?, ?, ?, ?)`;
            data = [ userType, name, phone, account, password ];
            newUser = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 新增用户失败 ${ JSON.stringify(data) }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("新增负责人失败 请检查输入数据是否冲突");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 新增用户成功 ${ JSON.stringify(data) }`);
        // 新增一个科室
        try {
            sql = `INSERT INTO Department (name, adminId, cityId, hospital, departmentID) VALUES (?, ?, ?, ?, ?)`;
            data = [ department, newUser.insertId, cityId, hospital, departmentID ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 新增科室失败 ${ JSON.stringify(data) }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("新增科室失败 请检查输入数据");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 新增科室成功 ${ JSON.stringify(data) }`);
        // 所有操作成功 提交事务
        await mysql.commitTransaction(connection);
        return result;
    },
    addHospital(reqId, hospital, department, departmentID, cityId, adminId) {
        let sql = `insert into Department(name, adminId, cityId, hospital, departmentID) values (?, ?, ?, ?, ?)`;
        let data = [ department, adminId, cityId, hospital, departmentID ];
        return mysql.sqlExec(sql, data);
    },
    delHospital(reqId, departmentId) {
        let sql = `update Department set is_del = ?, del_pk = ? where id = ? and is_del = ?`;
        let data = [ 'true', departmentId, departmentId, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    changeHospital(reqId, departmentId, hospital, department, cityId, uid) {
        let sql = `update Department set name = ?, adminId = ?, cityId = ?, hospital = ? where id = ? and is_del = ?`;
        let data = [ department, uid, cityId, hospital, departmentId, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getHospital(reqId, id) {
        let sql = `select
                d.name as department,
                d.adminId as adminId,
                c.provinceId as provinceId,
                d.cityId as cityId,
                d.hospital as hospital,
                d.departmentID as departmentID,
                u.name as username,
                u.phone as phone,
                u.account as account,
                ut.name as userType,
                c.name as city,
                p.name as province
            from (
                select * from Department where id = ? and is_del = ?
            ) as d
            left join User as u on d.adminId = u.id
            left join UserType as ut on u.userTypeId = ut.id
            left join City as c on d.cityId = c.id
            left join Province as p on c.provinceId = p.id`;
        let data = [ id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getAllHospital(reqId, uid) {
        let sql = `select
                d.id as id,
                d.name as department,
                d.adminId as adminId,
                c.provinceId as provinceId,
                d.cityId as cityId,
                d.hospital as hospital,
                d.departmentID as departmentID,
                u.name as username,
                u.phone as phone,
                u.account as account,
                ut.name as userType,
                c.name as city,
                p.name as province
            from Department as d
            left join User as u on d.adminId = u.id
            left join UserType as ut on u.userTypeId = ut.id
            left join City as c on d.cityId = c.id
            left join Province as p on c.provinceId = p.id
            where d.is_del = ?
            order by d.id desc`;
        let data = [ 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getAllHospital_usePower(reqId, uid) {
        let sql = `select
                d.id as id,
                d.name as department,
                d.adminId as adminId,
                c.provinceId as provinceId,
                d.cityId as cityId,
                d.hospital as hospital,
                d.departmentID as departmentID,
                u.name as username,
                u.phone as phone,
                u.account as account,
                ut.name as userType,
                c.name as city,
                p.name as province
            from Department as d
            left join User as u on d.adminId = u.id
            left join UserType as ut on u.userTypeId = ut.id
            left join City as c on d.cityId = c.id
            left join Province as p on c.provinceId = p.id
            where d.is_del = ? AND d.adminId = ?
            order by d.id desc`;
        let data = [ 'false', uid ];
        return mysql.sqlExec(sql, data);
    },
    getAllDevice(reqId, id) {
        let sql = `select
                d.id as id,
                d.devID as devID,
                dt.type as type,
                dt.name as name
            from Device as d
            left join DeviceType as dt on d.deviceTypeId = dt.id
            where d.departmentId = ? and d.is_del = ?`;
        let data = [ id, 'false' ];
        return mysql.sqlExec(sql, data);
    }
};

module.exports = hospitalDao;